import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Comparator;
import java.util.List;
import java.util.Properties;

/**
 * Creates or updates secondary tables and views that the web app uses.
 * 
 * @author dfhuynh
 */
public class Indexer {
    public static void main(String[] args) {
        Properties params = Utilities.parseParams(args);
        
        long start = System.currentTimeMillis();
        try {
            DatabaseUtilities.initializeDatabase(
        		params.getProperty("db"),
            	params.getProperty("user"),
            	params.getProperty("password"),
            	false);
            
            Statement statement = DatabaseUtilities.db.createStatement();
            /*
            System.out.println("Updating indexes ...");
            statement.executeQuery("REINDEX words_index");
            statement.executeQuery("REINDEX sentences_index");
            System.out.println("Done at " + formatTime(start, System.currentTimeMillis()));
            */
            
            System.out.println("Repopulating table 'followings'. This takes several minutes ...");
            statement.execute("DELETE FROM followings");
            /* This statement is too slow so we implement it in Java.
            DatabaseUtilities.db.exec(
                "INSERT INTO followings (sentence_id, word_before_id, word_after_id)" +
                " SELECT words.sentence_id, wordsBefore.id, words.id" +
                "  FROM words JOIN words AS wordsBefore" +
                "   ON words.sentence_id = wordsBefore.sentence_id" +
                "   WHERE words.in_sentence_token_index = wordsBefore.in_sentence_token_index + 1"
            );
            */
            PreparedStatement st = DatabaseUtilities.db.prepareStatement(
                "SELECT sentence_id, id, in_sentence_token_index, word, pos_tag FROM words " +
                "ORDER BY sentence_id");
            ResultSet rs = st.executeQuery();
            try {
                List<Word> words = new ArrayList<Indexer.Word>();
                long lastSentenceId = -1;
                
                while (rs.next()) {
                    long sentenceId = rs.getLong(1);
                    if (sentenceId != lastSentenceId) {
                        if (lastSentenceId >= 0) {
                            indexWordFollowingsPerSentence(lastSentenceId, words);
                        }
                        lastSentenceId = sentenceId;
                        words.clear();
                    }
                    Word word = new Word();
                    word.wordId = rs.getLong(2);
                    word.inSentenceIndex = rs.getLong(3);
                    word.word = rs.getString(4);
                    word.posTag = rs.getString(5);
                    words.add(word);
                }
                if (lastSentenceId >= 0) {
                    indexWordFollowingsPerSentence(lastSentenceId, words);
                }
            } finally {
            	rs.close();
                st.close();
            }
            System.out.println("Done at " + formatTime(start, System.currentTimeMillis()));
            /*
            System.out.println("Deleting existing views ...");
        	statement.execute("DROP VIEW IF EXISTS initial_word_length_facet");
        	statement.execute("DROP VIEW IF EXISTS initial_pos_tag_facet");
        	statement.execute("DROP VIEW IF EXISTS initial_frequent_word_view");
            
            System.out.println("Generating view 'initial_word_length_facet' ...");
            statement.execute(
                "CREATE ALGORITHM = TEMPTABLE VIEW initial_word_length_facet AS" +
                " SELECT LENGTH(word) AS l, COUNT(*) AS c FROM words" +
                " WHERE pos_tag <> \"PU\"" +
                " GROUP BY LENGTH(word)" +
                " ORDER BY COUNT(*) DESC" +
                " LIMIT 100"
            );
            
            System.out.println("Generating view 'initial_pos_tag_facet' ...");
            statement.execute(
                "CREATE ALGORITHM = TEMPTABLE VIEW initial_pos_tag_facet AS" +
                " SELECT pos_tag, COUNT(*) AS c FROM words" +
                " GROUP BY pos_tag" +
                " ORDER BY COUNT(*) DESC" +
                " LIMIT 100"
            );
            
            System.out.println("Generating view 'initial_frequent_word_view' ...");
            statement.execute(
                "CREATE ALGORITHM = TEMPTABLE VIEW initial_frequent_word_view AS" +
                " SELECT word, pos_tag, COUNT(*) AS c FROM words" +
                " WHERE pos_tag <> \"PU\"" +
                " GROUP BY word, pos_tag" +
                " ORDER BY COUNT(*) DESC" +
                " LIMIT 100"
            );
            
            System.out.println("Done at " + formatTime(start, System.currentTimeMillis()));
            */
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    
    final static class Word {
        long wordId;
        long inSentenceIndex;
        String word;
        String posTag;
    }
    
    final static private void indexWordFollowingsPerSentence(long sentenceId, List<Word> words) throws SQLException {
        if (words.size() < 2) {
            return;
        }
        Collections.sort(words, new Comparator<Word>() {
            @Override
            public int compare(Word o1, Word o2) {
                return Long.signum(o1.inSentenceIndex - o2.inSentenceIndex);
            }
        });
        
        Connection db = DatabaseUtilities.db;
        // db.exec("BEGIN TRANSACTION");
        for (int i = 1; i < words.size(); i++) {
            PreparedStatement st = db.prepareStatement("INSERT INTO followings" +
                " (sentence_id, word_before_id, word_after_id, word_before, word_after, pos_tag_before, pos_tag_after) " +
            	"VALUES(?, ?, ?, ?, ?, ?, ?)");
            Word wordBefore = words.get(i - 1);
            Word wordAfter = words.get(i);
            st.setLong(1, sentenceId);
            st.setLong(2, wordBefore.wordId);
            st.setLong(3, wordAfter.wordId);
            st.setString(4, wordBefore.word);
            st.setString(5, wordAfter.word);
            st.setString(6, wordBefore.posTag);
            st.setString(7, wordAfter.posTag);
            st.execute();
            st.close();
        }
    }
    
    final static private String formatTime(long start, long now) {
        long totalSeconds = (now - start) / 1000;
        long minutes = totalSeconds / 60;
        if (minutes < 1) {
            return totalSeconds + " seconds";
        } else {
            long seconds = totalSeconds % 60;
            return minutes + " min " + seconds + " sec";
        }
    }
}
